using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using XProject.Entity;
namespace XProject.DAO
{
    class EventDAO:DBAccess
    {
        #region sql command
        string SQL_SELECT_ID = "select max(ID) from Events";
        string SQL_SELECT_ALL = "select * from [Events] where AccountID=@accountID";
        string SQL_SELECT_BY_TIME_PERIOD = "select * from Events where AccountID=@accountId and StartTime>=@startTime and EndTime<=@endTime and TypeID=3";
        string SQL_SELECT_EVENT_BY_DATE = "select * from Events where  datepart('m',StartTime)=datepart('m',@startTime) and datepart('d',StartTime)=datepart('d',@startTime) and IsPublic=true";
        string SQL_SELECT_EVENT_BY_MONTH = "select * from Events where AccountID=@accountId and (datepart('m',StartTime)=@startMonth or LunarMonth=@lunarMonth) and TypeID<>1 and TypeID<>4";
        string SQL_SELECT_BY_LUNAR_DATE = "select * from Events where (LunarDay>@startDay and LunarMonth=@lunarMonth) or LunarMonth=lunarMonth+1 and LunarDay<15";
        string SQL_SELECT_PUBLIC = "select * from Events where StartTime>=@startTime and EndTime<=@endTime and IsPublic=@isPublic";
        string SQL_SELECT_BY_TYPE = "select * from [Events] where AccountID=@accountID and TypeID=@typeID";
        string SQL_INSERT = "insert into [Events](AccountID, StartTime, EndTime, LunarDay, LunarMonth, Content, Place, TypeID, Priority, [Note],IsPublic) values(@accountId,@startTime,@endTime, @lunarDay, @lunarMonth,@content ,@place, @typeID, @priority, @note,@isPublic)";
        string SQL_UPDATE = "update Events set StartTime=@startTime, EndTime=@endTime,LunarDay=@lunarDay, LunarMonth=@lunarMonth,Content=@content, Place=@place, TypeID=@typeId, Priority=@priority,[Note]=@note, IsPublic=@isPublic where ID=@eventID";
        string SQL_DELETE = "delete from Events where ID=@eventID";
        #endregion
        #region fields
        string message;
        #endregion
        #region properties
        public string Message
        {
            get { return message; }
        }
        #endregion
        #region methods
        /// <summary>
        /// Select all events belong to an account 
        /// </summary>
        /// <param name="accountID"></param>
        /// <returns></returns>
        public IList<Event> GetAllEvent(int accountID)
        {
            IList<Event> list = new List<Event>();
            try
            {
                OleDbCommand command = new OleDbCommand(SQL_SELECT_ALL, conn);
                if (Connect())
                {                  
                     command.Parameters.Add("@accountID",OleDbType.Integer).Value=accountID;
                      OleDbDataReader dr = command.ExecuteReader();
                    while (dr.Read())
                    {
                        Event e = new Event();
                        e.EventID = dr.GetInt32(0);
                        e.Account.AccountID = dr.GetInt32(1);
                        e.StartTime = dr.GetDateTime(2);
                        e.EndTime = dr.GetDateTime(3);
                        e.LunarDay = dr.GetByte(4);
                        e.LunarMonth = dr.GetByte(5);
                        e.Content = dr.GetString(6);
                        e.Place = dr.GetString(7);
                        e.Type.TypeID = dr.GetByte(8);
                        e.Priority = dr.GetByte(9);
                        e.Note = dr.GetString(10);
                        list.Add(e);

                    }
                    Disconnect();
                }               
            }
            catch (OleDbException ex)     { message = ex.Message;  }
            catch (Exception ex)   {    message = ex.Message;      }
            return list;
        }

        /// <summary>
        /// Select all events in a period of time
        /// </summary>
        /// <param name="accountID"></param>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <returns></returns>
        public IList<Event> GetEventByPeriodOfTime(int accountID,DateTime startTime,DateTime endTime)
        {
            IList<Event> list = new List<Event>();
            try
            {
                OleDbCommand command = new OleDbCommand(SQL_SELECT_BY_TIME_PERIOD, conn);
                command.Parameters.Add("@accountId", OleDbType.Integer).Value = accountID;
                command.Parameters.Add("@startTime", OleDbType.Date).Value = startTime;
                command.Parameters.Add("@endTime", OleDbType.Date).Value = endTime;
               
                if (Connect())
                {                  
                    
                    OleDbDataReader dr = command.ExecuteReader();
                    while (dr.Read())
                    {
                        Event e = new Event();
                        e.EventID = dr.GetInt32(0);
                        e.Account.AccountID = dr.GetInt32(1);
                        e.StartTime = dr.GetDateTime(2);
                        e.EndTime = dr.GetDateTime(3);
                        e.LunarDay = dr.GetByte(4);
                        e.LunarMonth = dr.GetByte(5);
                        e.Content = dr.GetString(6);
                        e.Place = dr.GetString(7);
                        e.Type.TypeID = dr.GetByte(8);
                        e.Priority = dr.GetByte(9);
                        e.Note = dr.GetString(10);
                        e.IsPublic = dr.GetBoolean(11);
                        list.Add(e);

                    }
                    Disconnect();
                }
            }
            catch (OleDbException ex) { message = ex.Message; }
            catch (Exception ex) { message = ex.Message; }
            return list;
        }
        /// <summary>
        /// Get event in specified date
        /// </summary>
        /// <param name="startTime"></param>
        /// <returns></returns>
        public IList<Event> GetEventByDate(DateTime startTime)
        {
            IList<Event> list = new List<Event>();
            try
            {
                OleDbCommand command = new OleDbCommand(SQL_SELECT_EVENT_BY_DATE, conn);
                command.Parameters.Add("@startTime", OleDbType.Date).Value = startTime;               
                if (Connect())
                {
                    OleDbDataReader dr = command.ExecuteReader();
                    while (dr.Read())
                    {
                        Event e = new Event();
                        e.EventID = dr.GetInt32(0);
                        e.Account.AccountID = dr.GetInt32(1);
                        e.StartTime = dr.GetDateTime(2);
                        e.EndTime = dr.GetDateTime(3);
                        e.LunarDay = dr.GetByte(4);
                        e.LunarMonth = dr.GetByte(5);
                        e.Content = dr.GetString(6);
                        e.Place = dr.GetString(7);
                        e.Type.TypeID = dr.GetByte(8);
                        e.Priority = dr.GetByte(9);
                        e.Note = dr.GetString(10);
                        e.IsPublic = dr.GetBoolean(11);
                        list.Add(e);
                    }
                    Disconnect();
                }
            }
            catch (OleDbException ex) { message = ex.Message; }
            catch (Exception ex) { message = ex.Message; }
            return list;
        }

        public IList<Event> GetEventInMonth(int accountID, int startMonth, int lunarMonth)
        {
            IList<Event> list = new List<Event>();
            try
            {
                OleDbCommand command = new OleDbCommand(SQL_SELECT_EVENT_BY_MONTH, conn);
                command.Parameters.Add("@accountId", OleDbType.Integer).Value = accountID;
                command.Parameters.Add("@startMonth", OleDbType.Date).Value = startMonth;
                command.Parameters.Add("@lunarMonth", OleDbType.Date).Value = lunarMonth;

                if (Connect())
                {

                    OleDbDataReader dr = command.ExecuteReader();
                    while (dr.Read())
                    {
                        Event e = new Event();
                        e.EventID = dr.GetInt32(0);
                        e.Account.AccountID = dr.GetInt32(1);
                        e.StartTime = dr.GetDateTime(2);
                        e.EndTime = dr.GetDateTime(3);
                        e.LunarDay = dr.GetByte(4);
                        e.LunarMonth = dr.GetByte(5);
                        e.Content = dr.GetString(6);
                        e.Place = dr.GetString(7);
                        e.Type.TypeID = dr.GetByte(8);
                        e.Priority = dr.GetByte(9);
                        e.Note = dr.GetString(10);
                        e.IsPublic = dr.GetBoolean(11);
                        list.Add(e);

                    }
                    Disconnect();
                }
            }
            catch (OleDbException ex) { message = ex.Message; }
            catch (Exception ex) { message = ex.Message; }
            return list;
        }
        /// <summary>
        /// Select by lunar time
        /// </summary>
        /// <param name="accountID"></param>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <returns></returns>
        public IList<Event> GetEventByLunarDate(int accountID, byte lunarMonth,byte startDay, byte endDay)
        {
            IList<Event> list = new List<Event>();
            try
            {
                OleDbCommand command = new OleDbCommand(SQL_SELECT_BY_LUNAR_DATE, conn);
                command.Parameters.Add("@accountId", OleDbType.Integer).Value = accountID;
                command.Parameters.Add("@lunarMonth",OleDbType.TinyInt).Value=lunarMonth;
                command.Parameters.Add("@startDay", OleDbType.TinyInt).Value = startDay;
                command.Parameters.Add("@endDay", OleDbType.TinyInt).Value = endDay;

                if (Connect())
                {
                    OleDbDataReader dr = command.ExecuteReader();
                    while (dr.Read())
                    {
                        Event e = new Event();
                        e.EventID = dr.GetInt32(0);
                        e.Account.AccountID = dr.GetInt32(1);
                        e.StartTime = dr.GetDateTime(2);
                        e.EndTime = dr.GetDateTime(3);
                        e.LunarDay = dr.GetByte(4);
                        e.LunarMonth = dr.GetByte(5);
                        e.Content = dr.GetString(6);
                        e.Place = dr.GetString(7);
                        e.Type.TypeID = dr.GetByte(8);
                        e.Priority = dr.GetByte(9);
                        e.Note = dr.GetString(10);
                        e.IsPublic = dr.GetBoolean(11);
                        list.Add(e);

                    }
                    Disconnect();
                }
            }
            catch (OleDbException ex) { message = ex.Message; }
            catch (Exception ex) { message = ex.Message; }
            return list;
        }
public IList<Event> GetPublicEvent(DateTime startTime,DateTime endTime, bool isPublic)
        {
            IList<Event> list = new List<Event>();
            try
            {
                OleDbCommand command = new OleDbCommand(SQL_SELECT_PUBLIC, conn);
               
                command.Parameters.Add("@startTime", OleDbType.Date).Value = startTime;
                command.Parameters.Add("@endTime", OleDbType.Date).Value = endTime;
                command.Parameters.Add("@isPublic", OleDbType.Boolean).Value = isPublic;               
                if (Connect())
                {                  
                    
                    OleDbDataReader dr = command.ExecuteReader();
                    while (dr.Read())
                    {
                        Event e = new Event();
                        e.EventID = dr.GetInt32(0);
                        e.Account.AccountID = dr.GetInt32(1);
                        e.StartTime = dr.GetDateTime(2);
                        e.EndTime = dr.GetDateTime(3);
                        e.LunarDay = dr.GetByte(4);
                        e.LunarMonth = dr.GetByte(5);
                        e.Content = dr.GetString(6);
                        e.Place = dr.GetString(7);
                        e.Type.TypeID = dr.GetByte(8);
                        e.Priority = dr.GetByte(9);
                        e.Note = dr.GetString(10);
                        e.IsPublic = dr.GetBoolean(11);
                        list.Add(e);

                    }
                    Disconnect();
                }
            }
            catch (OleDbException ex) { message = ex.Message; }
            catch (Exception ex) { message = ex.Message; }
            return list;
        }
        public IList<Event> GetEventByType(int accountID, EventType type)
        {
            IList<Event> list = new List<Event>();
            try
            {
                OleDbCommand command = new OleDbCommand(SQL_SELECT_BY_TYPE, conn);
                if (Connect())
                {
                    command.Parameters.Add("@accountID", OleDbType.Integer).Value = accountID;
                    command.Parameters.Add("@typeId", OleDbType.TinyInt).Value = type.TypeID;
                    OleDbDataReader dr = command.ExecuteReader();
                    while (dr.Read())
                    {
                        Event e = new Event();
                        e.EventID = dr.GetInt32(0);
                        e.Account.AccountID = dr.GetInt32(1);
                        e.StartTime = dr.GetDateTime(2);
                        e.EndTime = dr.GetDateTime(3);
                        e.LunarDay = dr.GetByte(4);
                        e.LunarMonth = dr.GetByte(5);
                        e.Content = dr.GetString(6);
                        e.Place = dr.GetString(7);
                        e.Type.TypeID = dr.GetByte(8);
                        e.Priority = dr.GetByte(9);
                        e.Note = dr.GetString(10);
                        e.IsPublic = dr.GetBoolean(11);
                        list.Add(e);

                    }
                    Disconnect();
                }
            }
            catch (OleDbException ex) { message = ex.Message; }
            catch (Exception ex) { message = ex.Message; }
            return list;
        }
        /// <summary>
        /// Insert new event
        /// </summary>
        /// <param name="e"></param>
        /// <returns></returns>
        public int Insert(Event e)
        {
            int effectedRows = 0;
            try
            {
                OleDbCommand command = new OleDbCommand(SQL_INSERT, conn);
                command.Parameters.Add("@accountId",OleDbType.Integer).Value=e.Account.AccountID;
                command.Parameters.Add("@startTime",OleDbType.Date).Value=e.StartTime;
                command.Parameters.Add("@endTime",OleDbType.Date).Value=e.EndTime;
                command.Parameters.Add("@lunarDay",OleDbType.TinyInt).Value=e.LunarDay;
                command.Parameters.Add("@lunarMonth",OleDbType.TinyInt).Value=e.LunarMonth;
                command.Parameters.Add("@content",OleDbType.VarChar).Value=e.Content;
                command.Parameters.Add("@place",OleDbType.VarChar).Value=e.Place;
                command.Parameters.Add("@typeID", OleDbType.TinyInt).Value = e.Type.TypeID;
                command.Parameters.Add("@priority",OleDbType.TinyInt).Value=e.Priority;
                command.Parameters.Add("@note",OleDbType.VarChar).Value=e.Note;
                command.Parameters.Add("@isPublic",OleDbType.Boolean).Value=e.IsPublic;
                if (Connect())
                {
                    effectedRows = command.ExecuteNonQuery();
                    command.CommandText = SQL_SELECT_ID;
                    e.EventID = int.Parse(command.ExecuteScalar().ToString());
                    Disconnect();
                }                
            }
            catch (OleDbException ex)
            {
                message = ex.Message;
            }
            catch (Exception ex) { message=ex.Message;}
            return effectedRows;
        }
        /// <summary>
        /// Update an event
        /// </summary>
        /// <param name="e"></param>
        /// <returns></returns>
        public int Update(Event e)
        {
            int effectedRows = 0;
            try
            {
                OleDbCommand command = new OleDbCommand(SQL_UPDATE, conn);                
                command.Parameters.Add("@startTime",OleDbType.Date).Value=e.StartTime;
                command.Parameters.Add("@endTime",OleDbType.Date).Value=e.EndTime;
                command.Parameters.Add("@lunarDay",OleDbType.TinyInt).Value=e.LunarDay;
                command.Parameters.Add("@lunarMonth",OleDbType.TinyInt).Value=e.LunarMonth;
                command.Parameters.Add("@content",OleDbType.VarChar).Value=e.Content;
                command.Parameters.Add("@place",OleDbType.VarChar).Value=e.Place;
                command.Parameters.Add("@typeID", OleDbType.TinyInt).Value = e.Type.TypeID;
                command.Parameters.Add("@priority",OleDbType.TinyInt).Value=e.Priority;
                command.Parameters.Add("@note",OleDbType.VarChar).Value=e.Note;
                command.Parameters.Add("@isPublic", OleDbType.Boolean).Value = e.IsPublic;
                command.Parameters.Add("@eventID",OleDbType.Integer).Value=e.EventID;
                
                if (Connect())
                {
                    effectedRows = command.ExecuteNonQuery();
                    Disconnect();
                }                
            }
            catch (OleDbException ex)
            {
                message = ex.Message;
            }
            catch (Exception ex) { message = ex.Message; }
            return effectedRows;
        }
        /// <summary>
        /// Delete an event
        /// </summary>
        /// <param name="e"></param>
        /// <returns></returns>
        public int Delete(Event e)
        {
            int effectedRows = 0;
            try
            {
                OleDbCommand command = new OleDbCommand(SQL_DELETE, conn);
                command.Parameters.Add("@eventID",OleDbType.Integer).Value=e.EventID;
                if (Connect())
                {
                    effectedRows = command.ExecuteNonQuery();                    
                }                
            }
            catch (OleDbException ex)
            {
                message = ex.Message;
            }
            catch (Exception ex) { message = ex.Message; }
            return effectedRows;
        }
        #endregion
    }
}
